package com.jonniespratley.flexdatabasemanager.services
{
	import com.adobe.net.URI;
	import com.adobe.serialization.json.JSON;
	import com.jonniespratley.flexdatabasemanager.model.ModelLocator;
	import com.jonniespratley.flexdatabasemanager.utils.PagedCollection;
	import com.jonniespratley.flexdatabasemanager.vo.BackupVO;
	import com.jonniespratley.flexdatabasemanager.vo.DatabaseVO;
	import com.jonniespratley.flexdatabasemanager.vo.FieldVO;
	import com.jonniespratley.flexdatabasemanager.vo.ServerVO;
	import com.jonniespratley.flexdatabasemanager.vo.TableCheckVO;
	import com.jonniespratley.flexdatabasemanager.vo.TableStatusVO;
	import com.jonniespratley.flexdatabasemanager.vo.UserVO;
	
	import mx.collections.ArrayCollection;
	import mx.controls.Alert;
	import mx.managers.CursorManager;
	import mx.rpc.AsyncToken;
	import mx.rpc.Responder;
	import mx.rpc.events.FaultEvent;
	import mx.rpc.events.ResultEvent;
	import mx.rpc.http.mxml.HTTPService;
	import mx.utils.ObjectUtil;
	
	public class DatabaseManagerService
	{		
		[Bindable] private var model:ModelLocator = ModelLocator.getInstance();
		
		//Internal Management
		private var serviceURL:URI;
		private var dbHost:String;
		private var dbPort:Number;
		private var dbUsername:String;
		private var dbPassword:String;
		private var dbServiceUrl:String;
		private var dbDatabase:String;
		private var dbTable:String;
		private var dbQuery:String;
		private var dbManagerService:HTTPService;
		private var responder:Responder;
			
		public function DatabaseManagerService( host:String = '', port:Number = 3306, username:String = '', password:String = '' )
		{
			this.dbHost = host;
			this.dbUsername = username;
			this.dbPassword = password;
		}
		
		private function sendQuery( whatMode:String, whatDatabase:String, whatTable:String,	whatQuery:String, resultHandler:Function ):void
		{
			//Required
			serviceURL = new URI( 'service/MySQLRESTService.php' );
			serviceURL.setQueryValue( 'h', dbHost );
			serviceURL.setQueryValue( 'u', dbUsername );
			serviceURL.setQueryValue( 'p', dbPassword );
			
			//Optional
			if ( whatMode != null ) serviceURL.setQueryValue( 'm', whatMode );
			if ( whatDatabase != null ) serviceURL.setQueryValue( 'd', whatDatabase ); 
			if ( whatTable != null ) serviceURL.setQueryValue( 't', whatTable ); 
			if ( whatQuery != null ) serviceURL.setQueryValue( 'q', whatQuery );

			//Service
			dbManagerService  = new HTTPService();
			dbManagerService.url = serviceURL.toString();
			dbManagerService.useProxy = false;
			//dbManagerService.showBusyCursor = true;
			
			//register event handlers
			dbManagerService.addEventListener( FaultEvent.FAULT, onFault );
			dbManagerService.addEventListener( ResultEvent.RESULT, resultHandler );
			
			//set up the token
			var token:AsyncToken = dbManagerService.send();
				token.mode = whatMode;
				token.database = whatDatabase;
				token.table = whatTable;
				token.sent = new Date();
			
			
			
			trace( '\n------------------------------------------------------' + 
					'\nSending service....' + 
					'\nMode: ' + whatMode + 
					'\nDatabase: ' + whatDatabase + 
					'\nTable: ' + whatTable + 
					'\nTimestamp: ' + new Date()+ 
					'------------------------------------------------------\n' );
		}
		
		public function analyzeTable( whatDatabase:String, whatTable:String ):void
		{
			sendQuery( 'optimizeTable', whatDatabase, whatTable, null, onResult_analyzeTable );
		}
		
		public function getTableColumns( whatDatabase:String, whatTable:String ):void
		{
			sendQuery( 'getTableColumns', whatDatabase, whatTable, null, onResult_getTableColumns );
		}
		
		public function checkLogin():void
		{
			sendQuery( 'checkLogin', null, null, null, onResult_checkLogin );
		}
		
		public function getUserInfo( whatUser:String ):void
		{
			sendQuery( 'getUserInfo', null, null, whatUser, onResult_getUserInfo );
		}
	
		public function getDatabases():void
		{			
			sendQuery( 'getDatabases', null, null, null, onResult_getDatabases );
		}
			
		public function getDatabasesAndTables():void
		{			
			sendQuery( 'getDatabasesAndTables', null, null, null, onResult_getDatabasesAndTables );
		}
		
		public function getTables( whatDatabase:String ):void
		{
			sendQuery( 'getTables', whatDatabase, null, null, onResult_getTables );
		}
		
		public function showTableStatus( whatDatabase:String ):void
		{			
			sendQuery( 'showTableStatus', whatDatabase, null, null, onResult_showTableStatus );
		}
		
		public function optimizeTable( whatDatabase:String, whatTable:String ):void
		{
			sendQuery( 'optimizeTable', whatDatabase, whatTable, null, onResult_optimizeTable );
		}
		
		public function repairTable( whatDatabase:String, whatTable:String ):void
		{
			sendQuery( 'repairTable', whatDatabase, whatTable, null, onResult_repairTable );
		}
		
		public function checkTable( whatDatabase:String, whatTable:String ):void
		{
			sendQuery( 'checkTable', whatDatabase, whatTable, null, onResult_checkTable );
		}
		
		public function getTableData( whatDatabase:String, whatTable:String ):void
		{
			sendQuery( 'getTableData', whatDatabase, whatTable, null, onResult_getTableData );
		}
		
		public function describeTable( whatDatabase:String, whatTable:String ):void
		{
			sendQuery( 'describeTable', whatDatabase, whatTable, null, onResult_describeTable );
		}
	
		public function executeQuery( whatQuery:String ):void
		{
			sendQuery( 'executeQuery', null, null, whatQuery, onResult_executeQuery );
		}	
		
		public function executeQueryToXML( whatQuery:String ):void
		{
			sendQuery( 'executeQueryToXML', null, null, whatQuery, onResult_executeQueryToXML );
		}	
		
		public function executeQueryToJSON( whatQuery:String ):void
		{
			sendQuery( 'executeQueryToJSON', null, null, whatQuery, onResult_executeQueryToJSON );
		}		
		
		public function dropDatabase( whatDatabase:String ):void
		{
			sendQuery( 'dropDatabase', whatDatabase, null, null, onResult_dropDatabase );
		}		
		
		public function createDatabase( whatDatabase:String ):void
		{
			sendQuery( 'createDatabase', whatDatabase, null, null, onResult_createDatabase );
		}
		
		public function showSystemProcesses():void
		{
			sendQuery( 'showSystemProcess', null, null, null, onResult_showSystemProcesses );
		}
		
		public function showSystemStatus():void
		{
			sendQuery( 'showSystemStatus', null, null, null, onResult_showSystemStatus );
		}
		
		public function showSystemUsers():void
		{
			sendQuery( 'showSystemUsers', null, null, null, onResult_showSystemUsers );
		}
		
		public function showSystemVariables():void
		{
			sendQuery( 'showSystemVariables', null, null, null, onResult_showSystemVariables );
		}
				
		public function getServerInfo():void
		{
			sendQuery( 'getServerInfo', null, null, null, onResult_getServerInfo );	
		}
		
		public function getTableIndex( whatDatabase:String, whatTable:String ):void
		{
			sendQuery( 'getTableIndex', whatDatabase, whatTable, null, onResult_getTableIndex );	
		}
		
		public function getBytes():void
		{
			sendQuery( 'getBytes', null, null, null, onResult_getBytes );	
		}
		
		public function getCommands():void
		{
			sendQuery( 'getCommands', null, null, null, onResult_getCommands );	
		}
		
		public function getConnections():void
		{
			sendQuery( 'getConnections', null, null, null, onResult_getConnections );	
		}
		
		public function getInnoDb():void
		{
			sendQuery( 'getInnoDb', null, null, null, onResult_getInnoDb );	
		}
		
		public function getKeys():void
		{
			sendQuery( 'getKeys', null, null, null, onResult_getKeys );	
		}
		
		public function getOpen():void
		{
			sendQuery( 'getOpen', null, null, null, onResult_getOpen );	
		}
		
		public function getPerformance():void
		{
			sendQuery( 'getPerformance', null, null, null, onResult_getPerformance );	
		}
		
		public function getQcache():void
		{
			sendQuery( 'getQcache', null, null, null, onResult_getQcache );	
		}
		
		public function getShowCommands():void
		{
			sendQuery( 'getShowCommands', null, null, null, onResult_getShowCommands );	
		}
		
		public function getSort():void
		{
			sendQuery( 'getSort', null, null, null, onResult_getSort );	
		} 
		
		public function getTemp():void
		{
			sendQuery( 'getTemp', null, null, null, onResult_getTemp );	
		} 
		
		public function getThreads():void
		{
			sendQuery( 'getThreads', null, null, null, onResult_getThreads );	
		}
		
		public function getGeneralVariables():void
		{
			sendQuery( 'getGeneralVariables', null, null, null, onResult_getGeneralVariables );	
		} 
		
		public function getDatabaseBackups( whatDatabase:String ):void
		{
			sendQuery( 'getDatabaseBackups', whatDatabase, null, null, onResult_getDatabaseBackups );	
		}
		
		public function removeBackup( whatDatabase:String, whatFile:String ):void
		{
			sendQuery( 'removeBackup', whatDatabase, null, whatFile, onResult_getDatabaseBackups );	
		}
		
		public function createBackup( whatDatabase:String, whatTable:String, whatType:String ):void
		{
			sendQuery( 'createBackup', whatDatabase, whatTable, whatType, onResult_createBackup );	
		}
		
		public function killProcess( whatProcess:String ):void
		{
			sendQuery( 'createBackup', null, null, whatProcess, onResult_killProcess );	
		}
			
			
			
		/** *******************************************************
		 * 
		 * ALL RESULT HANDLERS FOR ALL METHODS,
		 * RESULT HANDLERS ALL UPDATE THE MODEL AND HANDLE
		 * ERRORS PROPERLY
		 * 
		 ******************************************************* */	
			
			
			
			
		private function onResult_getDatabases( event:ResultEvent ):void
		{ 
			CursorManager.removeBusyCursor();
			
			model.databaseCollection = parseJSONResult( event.result );	
		}
		
		private function onResult_getDatabasesAndTables( event:ResultEvent ):void
		{ 
			CursorManager.removeBusyCursor();
			
			if ( event.result is String )
			{
				var rawData:String = String( event.result ); 
				var jsonArray:Array = ( JSON.decode( rawData ) as Array );
				var tempAC:ArrayCollection = new ArrayCollection();
			
				for ( var s:String in jsonArray )
				{		
					tempAC.addItem( new DatabaseVO( jsonArray[s] ) );	
				}
				model.databasesAndTablesCollection = tempAC;
			}
		}
			
		private function onResult_getTables( event:ResultEvent ):void
		{
			CursorManager.removeBusyCursor();
					
			if ( event.result is String )
			{
				var rawData:String = String( event.result ); 
				var jsonArray:Array = ( JSON.decode( rawData ) as Array );
				var tempAC:ArrayCollection = new ArrayCollection();
			
				for ( var s:String in jsonArray )
				{		
					//tempAC.addItem( new TableVO( jsonArray[s] ) );	
					tempAC.addItem( new DatabaseVO( jsonArray[s] ) );
				}
				model.tableAndFieldsCollection = tempAC;
			}
		}
		
		private function onResult_getTableData( event:ResultEvent ):void
		{ 
			CursorManager.removeBusyCursor();
			
			model.tableDataCollection = parseJSONResult( event.result );
			/* if ( event.result is String )
			{
				//get the raw JSON data and cast to String
				var rawData:String = String( event.result );

				//decode the data to ActionScript 
				var tempArray:Array = ( JSON.decode( rawData ) as Array );
			
				model.tableDataPagedCollection = new PagedCollection( tempArray );
				model.tableDataPagedCollection.refresh();
				
			} */
		}
		
		private function onResult_showTableStatus( event:ResultEvent ):void
		{
			CursorManager.removeBusyCursor();
	
			if ( event.result is String )
			{
				var rawData:String = String( event.result ); 
				var jsonArray:Array = ( JSON.decode( rawData ) as Array );
				var tempAC:ArrayCollection = new ArrayCollection();
			
				for ( var s:String in jsonArray )
				{		
					tempAC.addItem( new TableStatusVO( jsonArray[s] ) );	
				}
				model.tableStatusCollection = tempAC;
			}
		}
		
		private function onResult_getTableColumns( event:ResultEvent ):void
		{
			CursorManager.removeBusyCursor();
			
			if ( event.result is String )
			{
				var rawData:String = String( event.result ); 
				var jsonArray:Array = ( JSON.decode( rawData ) as Array );
				var tempAC:ArrayCollection = new ArrayCollection();
			
				for ( var s:String in jsonArray )
				{		
					tempAC.addItem( new FieldVO( jsonArray[s] ) );	
				}
				model.tableColumnsCollection = tempAC;
			}
		}
				
		private function onResult_describeTable( event:ResultEvent ):void
		{ 
			CursorManager.removeBusyCursor();
			
			model.tableColumnsCollection = parseJSONResult( event.result );
		}
		
		private function onResult_getTableIndex( event:ResultEvent ):void
		{ 
			CursorManager.removeBusyCursor();
			
			model.tableIndexCollection = parseJSONResult( event.result );
		}
		
		private function onResult_executeQuery( event:ResultEvent ):void
		{ 
			CursorManager.removeBusyCursor();
			
			model.queryCollection = parseJSONResult( event.result );
			
		}
		
		private function onResult_executeQueryToXML( event:ResultEvent ):void
		{ 
			CursorManager.removeBusyCursor();
			
			model.queryXMLCollection = event.result as XML;	
		}
		
		private function onResult_executeQueryToJSON( event:ResultEvent ):void
		{ 
			CursorManager.removeBusyCursor();
			
			model.queryJSONCollection = event.result as String;	
		}
		
		private function onResult_createDatabase( event:ResultEvent ):void
		{
			Alert.show( dumpObject( event.result ) );
		}
		
		private function onResult_createTable( event:ResultEvent ):void
		{
			Alert.show( dumpObject( event.result ) );
		}

		private function onResult_showSystemStatus( event:ResultEvent ):void
		{
			CursorManager.removeBusyCursor();
		
			model.systemStatusCollection = parseJSONResult( event.result );
		}
		
		private function onResult_showSystemVariables( event:ResultEvent ):void
		{
			//Alert.show( dumpObject( event.result ) );
			CursorManager.removeBusyCursor();
			
			model.systemVariablesCollection = parseJSONResult( event.result );
		}
		
		private function onResult_showSystemUsers( event:ResultEvent ):void
		{
			//Alert.show( dumpObject( event.result ) );
			CursorManager.removeBusyCursor();
			
			model.systemUsersCollection = parseJSONResult( event.result );
		}
		
		private function onResult_showSystemProcesses( event:ResultEvent ):void
		{
			//Alert.show( dumpObject( event.result ) );
			CursorManager.removeBusyCursor();
			
			model.systemProcessesCollection = parseJSONResult( event.result );
		}
			
		private function onResult_getServerInfo( event:ResultEvent ):void
		{
			//Alert.show( dumpObject( event.result ) );
			CursorManager.removeBusyCursor();
			
			//model.mysqlInfoCollection = parseJSONResult( event.result );
			if ( event.result is String )
			{
				//get the raw JSON data and cast to String
				var rawData:String = String( event.result );

				//decode the data to ActionScript 
				var tempArray:Array = ( JSON.decode( rawData ) as Array );
				var s:ServerVO = new ServerVO();
					s.aMySQLClientV 		= tempArray[0]['aMySQLClientV'] as String;
					s.aMySQLHost 			= tempArray[0]['aMySQLHost'] as String;
					s.aPHPExtensionDir 		= tempArray[0]['aPHPExtensionDir'] as String;
					s.aPHPOs 				= tempArray[0]['aPHPOs'] as String;
					s.aPHPVersion 			= tempArray[0]['aPHPVersion'] as String;
					s.aServerName 			= tempArray[0]['aServerName'] as String;
					s.aServerSoftware 		= tempArray[0]['aServerSoftware'] as String;
					s.aDiskFreeSpace 		= tempArray[0]['aDiskFreeSpace'] as String;
					s.aDiskTotalSize 		= tempArray[0]['aDiskTotalSize'] as String;
				model.serverInfo = s;
			}
		}
		
		private function onResult_killProcess( event:ResultEvent ):void
		{
			//model.mysqlInfoCollection = parseJSONResult( event.result );
			if ( event.result is String )
			{
				//get the raw JSON data and cast to String
				var rawData:String = String( event.result );

				//decode the data to ActionScript 
				var tempArray:Array = ( JSON.decode( rawData ) as Array );
				var tc:TableCheckVO 	= new TableCheckVO();
					tc.aTable 			= tempArray[0]['Table'] as String;
					tc.aOperation 		= tempArray[0]['Op'] as String;
					tc.aMessageType 	= tempArray[0]['Msg_type'] as String;
					tc.aMessageText 	= tempArray[0]['Msg_text'] as String;
				
					Alert.show( tc.aMessageText, tc.aTable );
			}
		}
		
		private function onResult_analyzeTable( event:ResultEvent ):void
		{
			Alert.show( dumpObject( event.result ) );
		}
		
		private function onResult_checkTable( event:ResultEvent ):void
		{
			Alert.show( dumpObject( event.result ) );
		}
		
		private function onResult_repairTable( event:ResultEvent ):void
		{
			Alert.show( dumpObject( event.result ) );
		}
		
		private function onResult_optimizeTable( event:ResultEvent ):void
		{
			Alert.show( dumpObject( event.result ) );
		}
		
		private function onResult_general( event:ResultEvent ):void
		{
			Alert.show( dumpObject( event.result ) );
		}
		
		private function onResult_dropDatabase( event:ResultEvent ):void
		{
			Alert.show( dumpObject( event.result ) );
		}
		
		private function onResult_checkLogin( event:ResultEvent ):void
		{
			
		}
		
		private function onResult_getDatabaseBackups( event:ResultEvent ):void
		{
			CursorManager.removeBusyCursor();
			
			if ( event.result is String )
			{
				var rawData:String = String( event.result ); 
				var jsonArray:Array = ( JSON.decode( rawData ) as Array );
				var tempAC:ArrayCollection = new ArrayCollection();
			
				for ( var s:String in jsonArray )
				{		
					//tempAC.addItem( new TableVO( jsonArray[s] ) );	
					tempAC.addItem( new BackupVO( jsonArray[s] ) );
				}
				model.databaseBackupCollection = tempAC;
			}
		}
		
		private function onResult_createBackup( event:ResultEvent ):void
		{
			Alert.show( dumpObject( event.result ) );
			CursorManager.removeBusyCursor();
		}
		
		private function onResult_removeBackup( event:ResultEvent ):void
		{
			Alert.show( dumpObject( event.result ) );
			CursorManager.removeBusyCursor();
		}
		
		private function onResult_getBytes( event:ResultEvent ):void
		{
			//Alert.show( dumpObject( event.result ) );
			CursorManager.removeBusyCursor();
			
			model.mysqlBytesCollection = parseJSONResult( event.result );
		}
		
		private function onResult_getCommands( event:ResultEvent ):void
		{
			//Alert.show( dumpObject( event.result ) );
			CursorManager.removeBusyCursor();
			
			model.mysqlCommandsCollection = parseJSONResult( event.result );
		}
		
		private function onResult_getConnections( event:ResultEvent ):void
		{
			//Alert.show( dumpObject( event.result ) );
			CursorManager.removeBusyCursor();
			
			model.mysqlConnectionsCollection = parseJSONResult( event.result );
		}
		
		private function onResult_getInnoDb( event:ResultEvent ):void
		{
			//Alert.show( dumpObject( event.result ) );
			CursorManager.removeBusyCursor();
			
			model.mysqlInnoDbCollection = parseJSONResult( event.result );
		}
		
		private function onResult_getKeys( event:ResultEvent ):void
		{
			//Alert.show( dumpObject( event.result ) );
			CursorManager.removeBusyCursor();
			
			model.mysqlKeysCollection = parseJSONResult( event.result );
		}
		
		private function onResult_getOpen( event:ResultEvent ):void
		{
			//Alert.show( dumpObject( event.result ) );
			CursorManager.removeBusyCursor();
			
			model.mysqlOpenCollection = parseJSONResult( event.result );
		}
		
		private function onResult_getPerformance( event:ResultEvent ):void
		{
			//Alert.show( dumpObject( event.result ) );
			CursorManager.removeBusyCursor();
			
			model.mysqlPerformanceCollection = parseJSONResult( event.result );
		}
		
		private function onResult_getQcache( event:ResultEvent ):void
		{
			//Alert.show( dumpObject( event.result ) );
			CursorManager.removeBusyCursor();
			
			model.mysqlQcacheCollection = parseJSONResult( event.result );
		}
		
		private function onResult_getShowCommands( event:ResultEvent ):void
		{
			//Alert.show( dumpObject( event.result ) );
			CursorManager.removeBusyCursor();
			
			model.mysqlShowCommandsCollection = parseJSONResult( event.result );
		}
		
		private function onResult_getSort( event:ResultEvent ):void
		{
			//Alert.show( dumpObject( event.result ) );
			CursorManager.removeBusyCursor();
			
			model.mysqlSortCollection = parseJSONResult( event.result );
		}
		
		private function onResult_getTemp( event:ResultEvent ):void
		{
			//Alert.show( dumpObject( event.result ) );
			CursorManager.removeBusyCursor();
			
			model.mysqlTempCollection = parseJSONResult( event.result );
		}
		
		private function onResult_getThreads( event:ResultEvent ):void
		{
			//Alert.show( dumpObject( event.result ) );
			CursorManager.removeBusyCursor();
			
			model.mysqlThreadsCollection = parseJSONResult( event.result );
		}
		
		private function onResult_getUserInfo( event:ResultEvent ):void
		{
			//Alert.show( dumpObject( event.result ) );
			CursorManager.removeBusyCursor();
			
			//model.mysqlInfoCollection = parseJSONResult( event.result );
			if ( event.result is String )
			{
				//get the raw JSON data and cast to String
				var rawData:String = String( event.result );

				//decode the data to ActionScript 
				var tempArray:Array = ( JSON.decode( rawData ) as Array );
				var u:UserVO = new UserVO (
											dbHost,
											Number( dbPort ), 
											dbUsername,
											dbPassword,
											tempArray[0]['Fullname'] as String,
											tempArray[0]['Description'] as String,
											tempArray[0]['Email'] as String,
											tempArray[0]['Info'] as String,
											tempArray[0]['Icon'] as String );
					
				model.currentUser = u;
			}
		}
		 
		private function onResult_getGeneralVariables( event:ResultEvent ):void
		{
			//Alert.show( dumpObject( event.result ) );
			CursorManager.removeBusyCursor();
			
			model.generalVariablesCollection = parseJSONResult( event.result );	
		}
		
		/* *********************** Private Utility Methods ************************ */

		private function dumpObject( obj:Object ):String
		{
			return ObjectUtil.toString( obj );
		}
		
		private function parseJSONResult( jsonObj:Object ):ArrayCollection
		{
			if ( jsonObj is String )
			{
				//get the raw JSON data and cast to String
				var rawData:String = String( jsonObj );

				//decode the data to ActionScript 
				var tempArray:Array = ( JSON.decode( rawData ) as Array );
			
				//var tempAC:ArrayCollection = new ArrayCollection( tempArray );
				
			}
			return  new ArrayCollection( tempArray );
		}
		
		private function parseJSONToPagedCollection( jsonObj:Object ):PagedCollection
		{
			if ( jsonObj is String )
			{
				//get the raw JSON data and cast to String
				var rawData:String = String( jsonObj );

				//decode the data to ActionScript 
				var tempArray:Array = ( JSON.decode( rawData ) as Array );
			
				//var tempPC:PagedCollection = new PagedCollection( tempArray );
			}
			return new PagedCollection( tempArray );
		}
	
		private function onFault( event:FaultEvent ):void
		{
			CursorManager.removeBusyCursor();
			
			Alert.show( event.fault.faultDetail + '\n' + 
					event.fault.message + '\n' + 
					event.fault.rootCause, event.fault.faultCode );					
		}
	}
}